﻿var util = require('util');
var sqlite3 = require('sqlite3').verbose();
var Db = {
    createNew: function (db_config) {
        db_map = null;
        function log_prepare(tablename) {
            //console.log("prepare or create table %s is ok", tablename);
        }
        function db_create(sql, tablename) {
            db_info.run(sql, log_prepare(tablename));
        }
        function handle_table_leftjoin(table) {
            var sql = " select "
            var lfields = table.left.fields;
            var ltablename = table.left.name;
            var lalias = table.left.alias;
            var rtablename = table.right.name;
            var ralias = table.right.alias;
            sql += lfields;
            sql += ",";
            var rfields = table.right.fields;
            sql += rfields;
            sql += " from ";
            sql += ltablename;
            sql += " ";
            sql += lalias;
            sql += " left join ";
            sql += rtablename;
            sql += " ";
            sql += ralias;
            return sql;
        }
        function field_analyse(f, alias) {
            var str = "";
            str += f;
            //console.log(str);
            var fieldx = str.split(',');
            var slen = fieldx.length;
            var out = "";
            var si = 0;
            fieldx.map(function (item) {
                var text = item.trim();
                //console.log(text, text.length);
                out += alias + "." + text + " " + text;
                si++;
                if (si < slen)
                    out += ","
            });
            // console.log(out);
            return out;
        }
        function handle_table_leftjoin2(table) {
            var sql = " select "

            var lfields = field_analyse(table.left.fields, "a");
            var ltablename = table.left.name;
            var rtablename = table.right.name;

            sql += lfields;
            sql += ",";
            var rfields = field_analyse(table.right.fields, "b");
            sql += rfields;
            sql += " from ";
            sql += ltablename;
            sql += " a";
            sql += " left join ";
            sql += rtablename;
            sql += " b";
            return sql;
        }
        // 创建表的sql语句
        function sql_create(tablename, fields) {
            var ret = "create table if not exists ";
            ret += tablename;
            ret += "(";
            for (var key in fields) {
                ret += "[";
                ret += key;
                ret += "] ";

                var t = fields[key].type;
                if (t == undefined) {
                    //          console.log("key's type not defined");
                    t = "TEXT";
                }
                ret += t;
                ret += ",";
            }
            ret = ret.substr(0, ret.length - 1);
            ret += ")";
            return ret;
        }
        // 创建表创建视图
        function map_create() {
            console.log("init start");
            if (db_map == null) {
                db_map = new Map();
            }
            var define = db_config.define;
            var table_field = null;
            var table_view = null;
            if (define == null) {
                console.log("no define ,error!");
                return;
            }
            else {
                table_field = define.table_field;
                table_view = define.table_view;
            }


            if (table_field != null) {
                for (var i = 0; i < table_field.length; i++) {
                    let name = table_field[i].tablename;
                    let fields = table_field[i].fields;

                    let str_create = sql_create(name, fields);
                    //let str_select = sql_select(tablename);
                    var info = {};
                    info["fields"] = fields;//table_field[i].fields;
                    db_map.set(name, info);
                    //创建所有的表以及表中的字段
                    db_create(str_create, name);
                }
            }
            if (table_view != null) {
                for (var i = 0; i < table_view.length; i++) {
                    if (table_view[i].type == "left") {
                        var sql = "";
                        if (table_view[i].table.left.alias != undefined) {
                            sql = handle_table_leftjoin(table_view[i].table)
                        }
                        else {
                            sql = handle_table_leftjoin2(table_view[i].table);
                        }

                        sql += " ";
                        sql += table_view[i].where;
                        let viewname = table_view[i].tablename;
                        let view = "create view if not exists " + viewname + " as ";

                        sql = view + sql;
                        //console.log(sql);
                        db_create(sql, viewname);
                    }
                    else if (table_view[i].type == "group") {
                        let viewname = table_view[i].tablename;
                        var sql = "create view if not exists " + table_view[i].tablename;
                        sql += " as ";
                        sql += table_view[i].sql;
                        db_create(sql, viewname);
                    }
                }
            }
        }

        //公共函数
        function update_sql(tablename, data) {
            var dataArray = Object.keys(data);
            var i = 0;
            var ret = "UPDATE ";
            ret += tablename;
            ret += " SET ";

            for (var key in data) {
                i++;
                var strtype = "";
                ret += key;
                ret += "=";
                ret += "'" + data[key] + "'";
                if (i != dataArray.length) {
                    ret += ",";
                }
            }
            return ret;
        }
        //公共函数
        function run_sql(sql, callback) {
            db_info.run(sql, function (err) {
                if (err) {
                    console.log(err);
                    if (callback != null)
                        callback(-1, err);
                }
                else {
                    if (callback != null) {
                        callback(1, 'ok');
                    }
                }
            });
        }


        db_info = new sqlite3.Database(db_config.dbname, map_create);

        var db = {
            tableName: "",
            selectS: "",
            whereS: "",
            orerbyS: "",
            limitS: "",
            querySql: "",
            countSql: "",
            groupbyS: "",
            havingS: "",
            distinctS: false,
            trans: false,
            setNull: function () {
                this.tableName = "";
                this.selectS = "";
                this.whereS = "";
                this.orerbyS = "";
                this.limitS = "";
                this.querySql = "";
                this.countSql = "";
                this.groupbyS = "";
                this.havingS = "";
                this.distinctS = false;
            }
        };
        db.get = function (tbName, offset, rows) {
            if (tbName) {
                this.tableName = tbName;
            }
            if (rows) {
                this.limit(offset, rows);
            }
            this.getSql();
        }
        db.get_where = function (tbName, data) {
            if (tbName) {
                this.tableName = tbName;
            };
            if (data) {
                this.where(data);
            };
            this.getSql();
        }
        
        db.where_in = function (field, dataArray) {
            var i;
            if (this.whereS == "") {
                this.whereS = " WHERE " + field + " IN (";
            }
            var wx = "";
            //console.log(dataArray.length);
            for (i = 0; i < dataArray.length; i++) {
                wx += dataArray[i];
                wx += (i+1 == dataArray.length)?")":",";
            }
            this.whereS += wx;

        }



        function judge_1(key, value) {
            var f_symbol = ["!=", ">=", "<=", "=", ">", "<"];
            var para = key;
            for (var i = 0; i < f_symbol.length; i++) {
                var slen = f_symbol[i].length;
                var cp = value.substr(0, slen);
                console.log(cp);
                if (cp === f_symbol[i]) {
                    console.log("===");
                    para += cp;
                    var temp = value.substr(slen, value.length - slen);
                    console.log(temp);
                    //para += temp;
                    return { key: para, value: temp };
                    //return para;
                }
            }
            para += " like ";
            return { key: para, value: value };
        }

        function obj_where(data) {
            if (typeof (obj) != "object") {
                console.log(obj, "is not an object");
                return -1;
            }
            //var where = " where 1=1 ";
            var test = 0;
            var fieldnum = Object.keys(data).length;
            for (var key in data) {
                var value = data[key];
                var ret = judge_1(key, value);
                where += " and " + ret.para;
                where += ret.value;
                if (++test < fieldnum)
                    para + ",";
            }
            return where;
        }


        function obj_where_param(data) {
            if (typeof (obj) != "object") {
                console.log(obj, "is not an object");
                return -1;
            }
            //var where = " where 1=1 ";
            var test = 0;
            var values = [];
            var fieldnum = Object.keys(data).length;
            for (var key in data) {
                var value = data[key];
                var ret = judge_1(key, value);
                where += " and " + ret.para;
                where += " ? ";
                if (++test < fieldnum)
                    para + ",";
                values.push(ret.value);
            }
            return {where:where, value:values};
        }


        db.where = function (whereData) {
            if (typeof (whereData) == "string") {
                this.whereS = whereData;
                return;
            }
            if (this.whereS == "") {
                this.whereS = " WHERE 1=1 ";
            }

            this.whereS = obj_where(whereData);
        }
        db.or_where = function (whereData) {
            if (this.whereS == "") {
                this.whereS = " WHERE 1!=1 ";
            }
            for (var key in whereData) {
                var wx = '';
                wx += " OR ";
                wx += key;
                if (key.indexOf("!=") !== -1 || key.indexOf(">") !== -1 || key.indexOf("<") !== -1 || key.indexOf("=") !== -1) {
                    wx += " ";
                } else {
                    wx += " = ";
                }

                wx += "'"+ whereData[key] + "'";
                this.whereS += wx;
            }
        }
        db.select = function (select) {
            this.selectS = select == "" ? "*" : select;
        }
        db.distinct = function () {
            this.distinctS = true;
        }
        db.limit = function (offset, rows) {
            this.limitS = " LIMIT " + offset + "," + rows;
        }
        db.like = function (whereData) {
            if (this.whereS == "") {
                this.whereS = " WHERE 1=1 ";
            }
            for (var key in whereData) {
                var wx = ' AND ';
                wx += key;
                wx += " LIKE ";
                var likes = "'%" + whereData[key] + "%'"
                this.whereS += wx;
            }

        }
        db.or_like = function (whereData) {
            if (this.whereS == "") {
                this.whereS = " WHERE 1!=1 ";
            }
            for (var key in whereData) {
                var wx = '';
                wx += " OR ";
                wx += key;
                wx += " LIKE ";
                var likes = "'%" + whereData[key] + "%'"
                wx += pool.escape(likes);
                //wx += "  escape ";
                this.whereS += wx;
            }
        }
        db.order_by = function (field, sort) {
            this.orerbyS = " ORDER BY " + field + " " + sort + " ";
        }
        db.group_by = function (fieldNname) {
            this.groupbyS = " GROUP BY " + fieldNname + " ";
        }
        db.having = function (havingSql) {
            this.havingS = " HAVING  " + havingSql + " ";
        }
        db.from = function (tbName) {
            this.tableName = tbName;
        }
        db.getSql = function () {
            var sql = "";
            if (this.selectS == "") {
                this.selectS = "*";
            }
            if (this.tableName == "") {
                console.log("table name is null ");
                return;
            }
            if (this.distinctS) {
                sql = "SELECT  DISTINCT " + this.selectS + " FROM " + this.tableName + " ";
            } else {
                sql = "SELECT  " + this.selectS + " FROM " + this.tableName + " ";
            }
            //这里和mysql 不一样, 直接as count
            var coSql = "SELECT  COUNT(*) AS COUNT FROM  " + this.tableName;
            var queryCon = "";
            var queryCountSql = "";
            if (this.whereS != "") {
                queryCon += this.whereS;
                queryCountSql += this.whereS;
            }

            if (this.groupbyS != "") {

                queryCon += this.groupbyS;
                queryCountSql += this.groupbyS;
            }

            if (this.havingS != "") {

                queryCon += this.havingS;
                queryCountSql += this.havingS;
            }
            if (this.orerbyS != "") {
                queryCon += this.orerbyS;
            }
            if (this.limitS != "") {
                queryCon += this.limitS;
            }
            this.setNull();
            this.countSql = "";
            this.querySql = sql + queryCon;
            this.countSql = coSql + queryCountSql;
        }
        db.count_all_results = function (callback) {
            if (this.countSql == "") {
                console.log("countSql is null return ....");
                return;
            }
            db_info.get(sql, function (err, row) {
                if (err) {
                    console.log("err sql :", countSql);
                    callback(-1, 0);
                }
                else {
                    callback(1, row.count);
                }
            });
        }
        db.result = function (callback) {
            db_info.all(querySql, function (err, rows) {
                if (err) {
                    console.log("err sql:" + querySql);
                    callback(-1, err);
                } else {
                    callback(1, rows);
                    //var ret = JSON.stringify(results);
                    //callback(1, JSON.parse(ret))
                }
            })
        }
        db.row = function (callback) {
            db_info.get(querySql, function (err, row) {
                if (err) {
                    console.log("err sql:" + querySql);
                    callback(-1, err);
                } else {
                    callback(1, row)
                }
            });
        }
        //sql语句直接查询
        db.querys = function query(sql, options, callback) {
            db_info.all(sql, function (err, rows) {
                if (err) {
                    console.log("err sql:" + sql);
                    callback(-1, err);
                } else 
                 callback(1, rows);
            });
        }
        db.run = function (sql, callback) {
            run_sql(sql, callback);
        }

        db.trans_start = function () {
            db_info.run("BEGIN TRANSACTION");
            this.trans = true;
        }
        db.execTrans = function (callback) {
            //not support now
            //not support
         
            return;
            //db_info.run("BEGIN TRANSACTION");
            var stmt = db.prepare("INSERT INTO foo VALUES(?, ?)");
            for (var i = 0; i < count; i++) {
                stmt.run(i, randomString());
            }
            db.run("COMMIT TRANSACTION");
        }
        //
        db.execTrans2 = function (data, callback) {

        }


        //db.endTrans = function()
        function obj_str_with_d(obj, flag) {
            if (typeof (obj) != "object") {
                console.log(obj, "is not an object");
                return -1;
            }
            if (Array.prototype.isPrototypeOf(obj) == true) { //如果是数组
                console.log("is an array");
                return -1;
            }
            else { //可能是对象
                var cc = 0;
                if (flag === 1) {
                    for (var k in obj) {
                        cc++;
                    }
                    return cc;
                }
                else if (flag === 2) {
                    var fields = "";
                    for (var k in obj) {
                        fields += k; fields += ","
                    }
                    fields = fields.substr(0, fields.length - 1);
                    return fields;
                }
                else if (flag === 3) {
                    var value = [];
                    for (var k in obj) {
                        value.push(obj[k]);
                    }
                    return value;
                }
                return -1;
            }
        }

        /*
        //注意 data 可以是一个对象,也可以是一个数组
        //data 是一个数组[{field:value,field:value},{field:value,field:value}]
        //data 是一个对象 {field:value,field:value}
        暂时不支持前后对象字段名不同
        */
        db.insert = function (tablename, data, cb) {
            var isArray = false;
            if (typeof (data) != "object" ) {
                console.log("error not an object");
                return;
            }
            if (Array.prototype.isPrototypeOf(data) == true) { //如果又不是数组
                isArray = true;
            }
            var fieldnum = 0;
            if (isArray == true)
                fieldnum = obj_str_with_d(data[0], 1); //计算个数
            else
                fieldnum = obj_str_with_d(data, 1);
            //    console.log("the num is ", fieldnum);
            if (fieldnum <= 0)
                return false;
            var para = "";
            for (var i = 0; i < fieldnum; i++) {
                if (i != fieldnum - 1)
                    para += "?,";
                else
                    para += "?";
            }
            var fields = null;
            if (isArray == true)
                fields = obj_str_with_d(data[0], 2);
            else
                fields = obj_str_with_d(data, 2);

            var sql = util.format("insert into %s( %s ) values(%s)", tablename, fields, para);
            db_info.serialize(function () {
                var stmt = db_info.prepare(sql);
                if (isArray == true) { //是数组
                    for (var i = 0; i < data.length; i++) {
                        var value = obj_str_with_d(data[i], 3);
                        stmt.run(value, function (err) {
                            if (err) {
                                console.log(err, "the value is ", value);
                                if (cb != null) {
                                    cb(-1, err);
                                }

                            }
                        });
                    }
                }
                else { //只是对象
                    var value = obj_str_with_d(data, 3);
                    stmt.run(value, function (err) {
                        if (err) {
                            console.log(err, "the value is ", value);
                            if (cb != null) {
                                cb(-1, err);
                            }

                        }
                    });
                }
                stmt.finalize();
                if (cb != null)
                    cb(1, data);

            });
            return true;
        }

        //data 可以是数组,可以是对象,而且数组可以使用不同的字段
        db.insert_ext = function (tablename, data, cb) {
            var isArray = false;
            //var isObject = true;
            if (typeof (data) != "object") {
                console.log("error not an object");
                return;
            }
            if (Array.prototype.isPrototypeOf(data) == true) { //如果又不是数组
                isArray = true;
            }

            db_info.serialize(function () {
                if (isArray) {
                    for (var i = 0; i < data.length; i++) {
                        var fieldnum = obj_str_with_d(data[i], 1); //计算个数
                        var para = get_params(fieldnum);
                        var fields = obj_str_with_d(data[i], 2);
                        var sql = util.format("insert into %s( %s ) values(%s)", tablename, fields, para);
                        var stmt = db_info.prepare(sql);
                        var value = obj_str_with_d(data[i], 3);
                        stmt.run(value, function (err) {
                            if (err) {
                                console.log(err, "the value is ", value);
                                if (cb != null) {
                                    cb(-1, err);
                                    return;
                                }
                            }
                        });
                        stmt.finalize();
                    }
                    if (cb != null)
                        cb(1, data);
                }
                else {
                    var fieldnum = obj_str_with_d(data, 1); //计算个数
                    var para = get_params(fieldnum);
                    var fields = obj_str_with_d(data, 2);
                    var sql = util.format("insert into %s( %s ) values(%s)", tablename, fields, para);
                    var stmt = db_info.prepare(sql);
                    var value = obj_str_with_d(data, 3);
                    stmt.run(value, function (err) {
                        if (err) {
                            console.log(err, "the value is ", value);
                            if (cb != null) {
                                cb(-1, err);
                            }
                            else {
                                if (cb != null)
                                    cb(1, data);
                            }
                        }
                    });
                }

            });
            return true;
        }

        db.replace = function (tablename, data, callback) {
            if (typeof (data) != "object") { //如果不是对象直接出错
                console.log("error not an object");
                return;
            }
            var values = [];
            var params = ""; //(?,?,?,?)
            var fields = "";
            for (var key in data) {
                fields += key;
                fields += ",";
                params += "?,";
                values.push(data[key]);
            }
            fields = fields.substr(0, fields.length - 1);
            params = params.substr(0, params.length - 1);

            
            var sql = util.format("REPLACE INTO %s( %s ) values(%s)", tablename, fields, params);
            var stmt = db_info.prepare(sql);
            stmt.run(value, function (error) {
                if (error) {
                    console.log("error sql :%s", sql);
                    console.log(error);
                    if (callback != null)
                        callback(-1, error);
                }
                else {
                    stmt.finalize();
                    if (callback != null) {
                        var insertId = stmt.lastID;//获取自动生成的id
                        callback(1, insertId);
                    }
                }
            });
        }

        db.update = function (tablename, data, callback) {
            if (db.whereS == null) {
                console.log("where is null, please do not kidding me");
                return;
            }
            if (data == null) {
                console.log("data is null, please do not kidding me");
                return;
            }

            var sql = update_sql(tablename, data);

            if (sql == null) {
                console.log("update sql is null, error!");
                return;
            }

            sql += db.whereS;

            db.setNull();

            run_sql(sql, callback);
         
        }
        //删除
        db.delete = function (tablename, callback) {
            if (db.whereS == "") {
                console.log("where is null, please do not kidding me");
                return;
            }
            if (!tablename) {
                console.log("table name is null, please do not kidding me");
                return;
            }

            var sql = "DELETE FROM " + tablename;
            sql += db.whereS;
            db.setNull();
           
            run_sql(sql, callback);
           
        }

        //data只支持对象,不需要使用 where 方法
        db.delete_ext = function (tablename, data, callback) {
            //if (db.whereS == "") {
            //    console.log("where is null, please do not kidding me");
            //    return;
            //}
            if (!tablename) {
                console.log("table name is null, please do not kidding me");
                return;
            }
            if (typeof (data) != "object")  {
                console.log("error not an object or object is null");
                return;
            }
            var fieldnum = Object.keys(data).length;
            //{where:where, value:values};
            var ret = obj_where_param(data);
            //var para = "";
            //var t = 0;
            //var value = [];
            
            //for (var key in data) {
            //    t++;
            //    para += key;
            //    para += "=?";
            //    if (t < fieldnum)
            //        para + ",";
            //    value.push(data[key]);
            //}
            sql = util.format("delete from %s where %s", tablename, ret.where);

            var stmt = db_info.prepare(sql);
            var value = obj_str_with_d(data, 3);
            stmt.run(value, function (err) {
                if (err) {
                    console.log(err, "the value is ", value);
                    if (cb != null) {
                        cb(-1, err);
                    }

                }
            });
            stmt.finalize();
            if (callback != null)
                callback(1, data);

            return true;

        }


        db.empty_table = function (tablename, callback) {
            var sql = "DELETE FROM " + tablename;
            run_sql(sql, callback);
        }
        db.count_all = function (tablename, callback) {
            var sql = " SELECT COUNT(*) FROM " + tablename;
            run_sql(sql, callback);
        }
        db.create_table = function (define, callback) {
            //
            var sql = sql_create(define.tablename, define.fields);
            run_sql(sql, function (ret, info) {
                callback(ret, info);
            });
        }
        db.drop_table = function (tablename, callback) {
            var sql = "DROP TABLE IF EXISTS " + tablename;
            run_sql(sql, callback);
        }
        db.list_fields = function (tablename, callback) {
            var sql = "pragma table_info(";
            sql += tablename + ")";

            db_info.all(sql, function (err, rows) {
                if (err) {
                    return;
                }
                if (callback != null) {
                    callback(1, rows);
                }
            });
        }
        db.add_column = function (tablename, fields, callback) {
            var sql = "alter table ";
            sql += tablename;
            sql += " add " + fieldname + " " + type;
            if (db_info == null) {
                console.log("null error");
                return;
            }
            db_info.run(sql, function (err) {
                if (cb != null) {
                    cb(1, 'ok');
                }
            });
        }
        db.drop_column = function (tablename, fieldName, callback) {
            this.list_fields(tablename, function(n, rows){
                if(n == 1){ //成功
                    var fields = "";
                    for(var i = 0;i<rows.length;i++){
                        if(rows[i].name !=fieldName){
                            fields += rows[i].name;
                            fields +=",";
                        }
                    }
                    fields = fields.substr(0,fields.length-1);
                    var sql = util.format("create table temp%s as select %s from %s where 1=1",tablename, fields,tablename);

                    run_sql(sql, function (n1, ret1) {
                        if (n1 == 1) {
                            var sql1 = util.format("drop table %s", tablename);
                            run_sql(sql1, function (n2, ret2) {
                                var sql2 = util.format("alter table temp%s rename to %s",tablename,tablename);
                                run_sql(sql2);
                            });
                        }
                    });

                }
                else{

                }
            });
        }
        db.modify_column = function (tablename, oldFieldName, newField, callback) {
            //not support
        }
        return db;
    }
}


module.exports = Db;
